package com.eep.data;


import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import com.eep.inventory.*;
import com.eep.order.*;
import com.eep.common.*;
import com.eep.security.*;
import com.eep.logging.*;

public class DataServiceImpl implements DataService {

    /*
     * SQL Strings
     */
    static final String getTreesSQL = "SELECT * FROM Trees";
    static final String getSeedsSQL = "SELECT * FROM Seeds";
    static final String getShrubsSQL = "SELECT * FROM Shrubs";
    static final String getOrdersSQL = "SELECT * FROM Orders";
    static final String ORDER_TABLENAME = "Orders" ;
    static final String AUTH_TABLENAME = "Authorization";
    static final String USERS_TABLENAME = "Users";
    static final String SECURITYLOG_TABLENAME = "Security.EventLog";
    static final String ORDERLOG_TABLENAME = "OrderInfo.OrderEventLog";
    /*
     * Property keys
     */
    static final String DATABASE_HOST = "databaseHost";
    /*
     * Database connectivity
     */
    private DatabaseName databaseName;
    static String sqlServerIP;
    static Connection dbConn;

    
    public DataServiceImpl()
    {
        System.out.println("Testing ... DataServiceImpl is created.");
        
    }

    public void createConnection(DatabaseName dbName)
    {
        sqlServerIP = PropertyReader.getInstance().getString(DATABASE_HOST);
        try
        {
            databaseName = dbName;
            //load JDBC driver class for MySQL
            Class.forName( "com.mysql.jdbc.Driver" );
            String sourceURL = "jdbc:mysql://" + sqlServerIP + ":3306/"+ databaseName;
            dbConn = DriverManager.getConnection(sourceURL,"remote","remote_pass");

        }
        catch(SQLException e)
        {
             System.out.println("Error creating database connection "+e);

        }
        catch(ClassNotFoundException ex)
        {
             System.out.println("Could not find com.mysql.jdbc.Driver driver "+ex);
        }
    }

    private void convertOrderFromDB(OrderSummary order, ResultSet orderRS) throws SQLException
    {
        Customer cust = new Customer();
        order.setId(orderRS.getInt(1));
        order.setDate(orderRS.getString(2));
        //Set customer details
        cust.setFirstName(orderRS.getString(3));
        cust.setLastName(orderRS.getString(4));
        cust.setAddress(orderRS.getString(5));
        cust.setPhoneNumber(orderRS.getString(6));
        order.setCustomer(cust);
        //order(orderRS.getDouble(7));
        order.setShipped(orderRS.getBoolean(8));

        
    }
    private OrderDetailed convertOrderDetailFromDB(OrderDetailed order,String orderTableName) throws SQLException
    {
        //OrderDetailed detail= null;
        String sqlDetail ="SELECT * FROM " + orderTableName;
        ResultSet rs =null;
        try
        {
            rs = executeSQL(sqlDetail);

            while(rs.next())
            {
                OrderLineItem lineItem = new OrderLineItem();
                lineItem.setProductId(rs.getString(2));
                lineItem.setDescription(rs.getString(3));
                lineItem.setItemPrice(rs.getDouble(4));
                order.addOrderLineItem(lineItem);
            }

        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

         return   order;

    }
    private ResultSet executeSQL(String sqlStatement)
    {
        ResultSet rs = null;
        try
        {
            //Only perform opertations if db connection is open
            if(!dbConn.isClosed())
            {
                //Call database
                Statement sql = dbConn.createStatement();
                rs = sql.executeQuery(sqlStatement);                
            }
        }
        catch(SQLException e)
        {
            System.out.println("Error calling sql "+ sqlStatement);
            e.printStackTrace();
        }
        return rs;
    }
    private int executeSQLUpdate(String sqlStatement)
    {
        int returnValue  = -1;
        try
        {
            //Only perform opertations if db connection is open
            if(!dbConn.isClosed())
            {
                //Call database
                Statement sql = dbConn.createStatement();
                returnValue = sql.executeUpdate(sqlStatement);
            }
        }
        catch(SQLException e)
        {
            System.out.println("Error calling sql "+ sqlStatement);
            e.printStackTrace();
        }
        return returnValue;
    }
    public List<InventoryItem> getTrees()
    {
        List<InventoryItem> treesList=null;
        ResultSet treesRS;
        try
        {
           treesRS= executeSQL(getTreesSQL);
           
           treesList = new ArrayList<InventoryItem>();
            while (treesRS.next())
            {
                TreesInventoryItem tree= new TreesInventoryItem();
                tree.setProductId(treesRS.getString(1));
                tree.setDescription(treesRS.getString(2));
                tree.setUnitsAvailable(treesRS.getInt(3));
                tree.setCost(treesRS.getDouble(4));
                treesList.add(tree);
            }

        }
        catch(Exception e)
        {
            System.out.println("Error in returning getTrees" + e);
            return null;
        }
        return treesList;
        
    }
   public List<InventoryItem> getSeeds()
   {
        List<InventoryItem> seedsList=null;
        ResultSet seedsRS;
        try
        {
            seedsRS= executeSQL(getSeedsSQL);
            seedsList = new ArrayList<InventoryItem>();
            while (seedsRS.next())
            {
                SeedsInventoryItem seed= new SeedsInventoryItem();
                seed.setProductId(seedsRS.getString(1));
                seed.setDescription(seedsRS.getString(2));
                seed.setUnitsAvailable(seedsRS.getInt(3));
                seed.setCost(seedsRS.getDouble(4));
                seedsList.add(seed);
            }

        }
        catch(Exception e)
        {
            return null;
        }
        return seedsList;
   }
   
   public List<InventoryItem> getShrubs()
   {
        List<InventoryItem> shrubsList=null;
        ResultSet shrubsRS;
        try
        {
            shrubsRS= executeSQL(getShrubsSQL);
            shrubsList = new ArrayList<InventoryItem>();
            while (shrubsRS.next())
            {
                ShrubsInventoryItem shrub= new ShrubsInventoryItem();
                shrub.setProductId(shrubsRS.getString(1));
                shrub.setDescription(shrubsRS.getString(2));
                shrub.setUnitsAvailable(shrubsRS.getInt(3));
                shrub.setCost(shrubsRS.getDouble(4));
                shrubsList.add(shrub);
            }
        }
        catch(Exception e) {
            e.printStackTrace();
            return null;
        }
        return shrubsList;
   }

   public int createOrder(OrderDetailed order)
   {
       int order_id = -1;
       
       String createDetailSql = ( "CREATE TABLE " + order.getOrderTable() +
                            "(item_id int unsigned not null auto_increment primary key, " +
                            "product_id varchar(20), description varchar(80), " +
                            "item_price float(7,2) );");
       String insertOrderSummSql = ( "INSERT INTO orders (order_date, " + "first_name, " +
                        "last_name, address, phone, total_cost, shipped, " +
                        "ordertable) VALUES ( '" + order.getDate() + "', " +
                        "'" + order.getCustomer().getFirstName() + "', " + "'" + order.getCustomer().getLastName() + "', " +
                        "'" + order.getCustomer().getAddress() + "', " + "'" + order.getCustomer().getPhoneNumber() + "', " +
                        order.getCost() + ", " + false + ", '" + order.getOrderTable() +"' );");
       String insertDetailSql;
       int returnVal;
       try
        {
            //Create  order detail table
            returnVal = executeSQLUpdate(createDetailSql);
            
            //Create order table
            if(returnVal == 0)//value of 0 means table was created properly
            {
                returnVal = executeSQLUpdate(insertOrderSummSql);
            }

            if(returnVal >0)//order was inserted properly
            {
                //iterate over items in order
                //insert into order detail table
                for(int i=0;i<order.getOrderLineItemCount();i++)
                {
                    OrderLineItem lineItem = order.getOrderLineItem(i);
                    insertDetailSql =( "INSERT INTO " + order.getOrderTable() +
                                " (product_id, description, item_price) " +
                                "VALUES ( '" + lineItem.getProductId() + "', " + "'" +
                                lineItem.getDescription() + "', " + lineItem.getItemPrice() + " );");
                    returnVal = executeSQLUpdate(insertDetailSql);
                }
                if(returnVal >0 )
                {
                    ResultSet rs = executeSQL("Select order_id from orders where ordertable ='"+order.getOrderTable()+"';");
                    while(rs.next())
                    {
                        order_id=rs.getInt(1);
                    }
                }
                
            }
        }
        catch(Exception e)
        {
            System.out.println("Error creating new order for date :" + order.getDate() + e );
        }
        return order_id;
   }
   public OrderDetailed readOrder( int OrderId)
   {
       OrderDetailed order;
       ResultSet orderRS;
       String sql = "SELECT * FROM "+ORDER_TABLENAME +" where order_id= "+ OrderId;
        try
        {
            orderRS= executeSQL(sql);
            order = new OrderDetailed();            
            while (orderRS.next())
            {
               convertOrderFromDB(order, orderRS);
               convertOrderDetailFromDB(order,orderRS.getString(9));
            }
        }
        catch(Exception e) {
            e.printStackTrace();
            return null;
        }
       return order;
   }
   public List<OrderSummary> getOrdersSummary()
   {
       List<OrderSummary> ordersList=null;
       ResultSet orderRS;
       try
        {
            orderRS= executeSQL(getOrdersSQL);
            ordersList = new ArrayList<OrderSummary>();
            while (orderRS.next())
            {
                OrderSummary order= new OrderSummary();
                convertOrderFromDB(order, orderRS);
                ordersList.add(order);
            }
        }
        catch(Exception e) {
            e.printStackTrace();
            return null;
        }
       return ordersList;
   }


   public void updateOrder(int OrderId,Boolean isShipped)
   {
        String updateSql = "UPDATE " +ORDER_TABLENAME + " SET "
                            + " shipped =" + isShipped + " where order_id="+OrderId; //update shipping status

        int returnVal=-1;
        returnVal = executeSQLUpdate(updateSql);
        

   }
   
   public void createLogItem(LogEvent logEvent)
   {
      String insertSql = "INSERT INTO ";
      Calendar calendar = Calendar.getInstance();
      DateFormat df = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
      String timeStamp = df.format(calendar.getTime());
      String type= logEvent.getLogType();
      if(type.matches("Security"))
      {
          insertSql += SECURITYLOG_TABLENAME +"(Event_Date,Username,Action) VALUES ('"+timeStamp+"','"+logEvent.getUsername()+"','"+logEvent.getLogEventType()+"');";
      }
      else
      {
         insertSql += ORDERLOG_TABLENAME +" (Event_Date,Username,Order_id,Action) VALUES ('"+timeStamp+"','"+logEvent.getUsername()+"',"+logEvent.getOrderNumber()+",'"+logEvent.getLogEventType()+"');";
      }
                
      int returnVal = -1;
      returnVal = executeSQLUpdate(insertSql);

   }
   
   public List<LogEvent> listLog(LogType logType)
   {
       String selectSql = "SELECT * FROM ";
       List<LogEvent> log = new ArrayList<LogEvent>();
     
       switch(logType)
       {
           case Security:
            selectSql += " "+SECURITYLOG_TABLENAME + " ORDER BY event_date asc;";
            break;
           case Shipping:
               selectSql += " "+ ORDERLOG_TABLENAME + " where action ='Order Shipped' order by event_date asc";
               break;
           default:
                selectSql += " "+ ORDERLOG_TABLENAME + " where action !='Order Shipped' order by event_date asc";;
                break;
       }
      
      try
      {
           ResultSet rs = null;
           rs = executeSQL(selectSql);          
           while(rs.next())
           {
               LogEvent event=null;
               if(logType ==LogType.Security)
               {
                    event = new LogEvent(rs.getString(1), rs.getString(2),LogType.Security, getLogEventTypeEnum(rs.getString(3)),-1);
               }
               else if(logType == LogType.Shipping)
               {
                    event =new LogEvent(rs.getString(1), rs.getString(2),LogType.Shipping,getLogEventTypeEnum(rs.getString(4)),rs.getInt(3));
               }
               else
               {
                    event =new LogEvent(rs.getString(1), rs.getString(2),LogType.Order,getLogEventTypeEnum(rs.getString(4)),rs.getInt(3));
               }
               log.add(event);
           }

      }
      catch(Exception e)
      {
          e.printStackTrace();
      }
      return log;
   }
   public Session getUser(String username)
   {
       Session session = null;
       String sql = "SELECT * FROM " + USERS_TABLENAME + " WHERE Username ='"+username+"'";
       try
       {
           ResultSet rs=null;
           rs =executeSQL(sql);
           session = new Session();
           while(rs.next())
           {
               //only return username and password
               if(rs.getString(2) != null)
               {
                    session.setUsername(username);
                    session.setPassword(rs.getString(2));
               }

           }
           if(session.getPassword()==null)
           {
               session = null;
           }
       }
       catch(Exception e)
       {
             e.printStackTrace();
       }
       return session;
   }
   public  Boolean isUserAuthorized(String username,Session.Applications applicationName)
   {
       Boolean isAuthorized = false;
        String sql = "SELECT COUNT(*) FROM " + AUTH_TABLENAME + " WHERE Username ='"+username+"' and application ='"+applicationName+"'";
       try
       {
           ResultSet rs=null;
           rs =executeSQL(sql);
           while(rs.next())
           {
                isAuthorized = rs.getInt(1) >= 1 ? true : false ;
           }
       }
       catch(Exception e)
       {
             e.printStackTrace();
       }
       return isAuthorized;
   }
   private LogEventType getLogEventTypeEnum(String eventDescription)
    {
        LogEventType returnType = null;
        LogEventType[] values = LogEventType.values();
        for(LogEventType eventtype: values)
        {
            if(eventtype.getEventDescription().matches(eventDescription))
            {
                returnType= eventtype;
            }
        }
        return returnType;
    }
  
}

